package cn.dy.workatten.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import cn.dy.base.framework.db.DBConnectPool;
import cn.dy.base.framework.db.DBUtil;
import cn.dy.base.framework.db.util.DBExecuteUtil;
import cn.dy.workatten.pojo.DutyScoreSummary;

public class DutyScoreSummaryDao {

	protected static Logger logger = LoggerFactory.getLogger(DutyScoreSummaryDao.class);

	private DataSource dataSource;
	private JdbcTemplate jdbcTemplate;
	private NamedParameterJdbcTemplate namedJdbcTemplate;

	public DutyScoreSummaryDao() {
		this.dataSource = DBConnectPool.create().getDataSource();
		if (this.jdbcTemplate == null)
			this.jdbcTemplate = new JdbcTemplate(this.dataSource);
		if (this.namedJdbcTemplate == null) {
			this.namedJdbcTemplate = new NamedParameterJdbcTemplate(this.dataSource);
		}
	}

	/**
	 * 获得自增系列
	 * 
	 * @return
	 */
	public long getUniqueId() {
		long result = 0L;
		try {
			result = DBUtil.getSequence(new JdbcTemplate(this.dataSource), "seq_bz_duty_score_summary");
		} catch (DataAccessException e) {
			logger.error("获得加值班补贴汇总表自动生成的ID值失败", e);
		}
		return result;
	}

	/**
	 * 新增
	 * 
	 * @param scheduleCfg
	 * @return
	 */
	public int addDutyScoreSummary(DutyScoreSummary dutyScoreSummary) {
		DBExecuteUtil db = new DBExecuteUtil();
		int result = 0;

		try {
			String sql = "insert into bz_duty_score_summary(id,staff_id,dept_id,month,schedule_1_score,schedule_2_score,schedule_3_score,"
					+ "schedule_4_score,schedule_5_score,schedule_6_score,schedule_7_score,schedule_8_score,"
					+ "schedule_9_score,schedule_10_score,schedule_11_score,schedule_12_score,schedule_13_score,schedule_14_score,schedule_15_score,schedule_16_score,"
					+ "all_score,overtime_score,overtime_pay,create_id,create_time,midnight_score,schedule_1_day,schedule_2_day,schedule_3_day,schedule_4_day,schedule_5_day,schedule_6_day,"
					+ "schedule_7_day,schedule_8_day,schedule_9_day,schedule_10_day,schedule_11_day,schedule_12_day,schedule_13_day,schedule_14_day,schedule_15_day,schedule_16_day) "
					+ " values(:id,:staff_id,:dept_id,:month,:schedule_1_score,:schedule_2_score,:schedule_3_score,"
					+ ":schedule_4_score,:schedule_5_score,:schedule_6_score,:schedule_7_score,:schedule_8_score," 
					+ ":schedule_9_score,:schedule_10_score,:schedule_11_score,:schedule_12_score,:schedule_13_score,:schedule_14_score,:schedule_15_score,:schedule_16_score," 
					+ ":all_score,:overtime_score,:overtime_pay,:create_id,:create_time,:midnight_score,:schedule_1_day,:schedule_2_day,:schedule_3_day,:schedule_4_day,:schedule_5_day,:schedule_6_day,"
					+ ":schedule_7_day,:schedule_8_day,:schedule_9_day,:schedule_10_day,:schedule_11_day,:schedule_12_day,:schedule_13_day,:schedule_14_day,:schedule_15_day,:schedule_16_day)";

			Map resultMap = new HashMap();
			resultMap.put("id", getUniqueId());
			resultMap.put("staff_id", dutyScoreSummary.getStaff_id());
			resultMap.put("dept_id", dutyScoreSummary.getDept_id());
			resultMap.put("month", dutyScoreSummary.getMonth());
			
			resultMap.put("schedule_1_score", dutyScoreSummary.getSchedule_1_score());
			resultMap.put("schedule_2_score", dutyScoreSummary.getSchedule_2_score());
			resultMap.put("schedule_3_score", dutyScoreSummary.getSchedule_3_score());
			resultMap.put("schedule_4_score", dutyScoreSummary.getSchedule_4_score());
			resultMap.put("schedule_5_score", dutyScoreSummary.getSchedule_5_score());
			resultMap.put("schedule_6_score", dutyScoreSummary.getSchedule_6_score());
			resultMap.put("schedule_7_score", dutyScoreSummary.getSchedule_7_score());
			resultMap.put("schedule_8_score", dutyScoreSummary.getSchedule_8_score());
			resultMap.put("schedule_9_score", dutyScoreSummary.getSchedule_9_score());
			resultMap.put("schedule_10_score", dutyScoreSummary.getSchedule_10_score());
			resultMap.put("schedule_11_score", dutyScoreSummary.getSchedule_11_score());
			resultMap.put("schedule_12_score", dutyScoreSummary.getSchedule_12_score());
			resultMap.put("schedule_13_score", dutyScoreSummary.getSchedule_13_score());
			resultMap.put("schedule_14_score", dutyScoreSummary.getSchedule_14_score());
			resultMap.put("schedule_15_score", dutyScoreSummary.getSchedule_15_score());
			resultMap.put("schedule_16_score", dutyScoreSummary.getSchedule_16_score());
			
			resultMap.put("all_score", dutyScoreSummary.getAll_score());
			resultMap.put("overtime_score", dutyScoreSummary.getOvertime_score());
			resultMap.put("overtime_pay", dutyScoreSummary.getOvertime_pay());
			resultMap.put("create_id", dutyScoreSummary.getCreate_id());
			resultMap.put("create_time", dutyScoreSummary.getCreate_time());
			resultMap.put("midnight_score", dutyScoreSummary.getMidnight_score());
			
			resultMap.put("schedule_1_day", dutyScoreSummary.getSchedule_1_day());
			resultMap.put("schedule_2_day", dutyScoreSummary.getSchedule_2_day());
			resultMap.put("schedule_3_day", dutyScoreSummary.getSchedule_3_day());
			resultMap.put("schedule_4_day", dutyScoreSummary.getSchedule_4_day());
			resultMap.put("schedule_5_day", dutyScoreSummary.getSchedule_5_day());
			resultMap.put("schedule_6_day", dutyScoreSummary.getSchedule_6_day());
			resultMap.put("schedule_7_day", dutyScoreSummary.getSchedule_7_day());
			resultMap.put("schedule_8_day", dutyScoreSummary.getSchedule_8_day());
			resultMap.put("schedule_9_day", dutyScoreSummary.getSchedule_9_day());
			resultMap.put("schedule_10_day", dutyScoreSummary.getSchedule_10_day());
			resultMap.put("schedule_11_day", dutyScoreSummary.getSchedule_11_day());
			resultMap.put("schedule_12_day", dutyScoreSummary.getSchedule_12_day());
			resultMap.put("schedule_13_day", dutyScoreSummary.getSchedule_13_day());
			resultMap.put("schedule_14_day", dutyScoreSummary.getSchedule_14_day());
			resultMap.put("schedule_15_day", dutyScoreSummary.getSchedule_15_day());
			resultMap.put("schedule_16_day", dutyScoreSummary.getSchedule_16_day());
			
			result = db.executeSql(sql, resultMap);
		} catch (DataAccessException e) {
			logger.error("添加加值班补贴失败", e);
		}
		return result;
	}

	/**
	 * 修改
	 * 
	 * @return
	 */
	public int updateDutyScoreSummary(DutyScoreSummary dutyScoreSummary) {
		DBExecuteUtil db = new DBExecuteUtil();
		int result = 0;
		try {
			String sql = "update bz_duty_score_summary set staff_id=:staff_id,dept_id=:dept_id,month=:month,schedule_1_score=:schedule_1_score,schedule_2_score=:schedule_2_score,schedule_3_score=:schedule_3_score where id=:id";

			Map resultMap = new HashMap();
			resultMap.put("id", Long.valueOf(dutyScoreSummary.getId()));
			resultMap.put("staff_id", dutyScoreSummary.getStaff_id());
			resultMap.put("dept_id", dutyScoreSummary.getDept_id());
			resultMap.put("month", dutyScoreSummary.getMonth());
			
			resultMap.put("schedule_1_score", dutyScoreSummary.getSchedule_1_score());
			resultMap.put("schedule_2_score", dutyScoreSummary.getSchedule_2_score());
			resultMap.put("schedule_3_score", dutyScoreSummary.getSchedule_3_score());
			resultMap.put("schedule_4_score", dutyScoreSummary.getSchedule_4_score());
			resultMap.put("schedule_5_score", dutyScoreSummary.getSchedule_5_score());
			resultMap.put("schedule_6_score", dutyScoreSummary.getSchedule_6_score());
			resultMap.put("schedule_7_score", dutyScoreSummary.getSchedule_7_score());
			resultMap.put("schedule_8_score", dutyScoreSummary.getSchedule_8_score());
			resultMap.put("schedule_9_score", dutyScoreSummary.getSchedule_9_score());
			resultMap.put("schedule_10_score", dutyScoreSummary.getSchedule_10_score());
			resultMap.put("schedule_11_score", dutyScoreSummary.getSchedule_11_score());
			resultMap.put("schedule_12_score", dutyScoreSummary.getSchedule_12_score());
			resultMap.put("schedule_13_score", dutyScoreSummary.getSchedule_13_score());
			resultMap.put("schedule_14_score", dutyScoreSummary.getSchedule_14_score());
			resultMap.put("schedule_15_score", dutyScoreSummary.getSchedule_15_score());
			resultMap.put("schedule_16_score", dutyScoreSummary.getSchedule_16_score());
			
			resultMap.put("all_score", dutyScoreSummary.getAll_score());
			resultMap.put("overtime_score", dutyScoreSummary.getOvertime_score());
			resultMap.put("overtime_pay", dutyScoreSummary.getOvertime_pay());
			resultMap.put("create_id", dutyScoreSummary.getCreate_id());
			resultMap.put("create_time", dutyScoreSummary.getCreate_time());
			resultMap.put("midnight_score", dutyScoreSummary.getMidnight_score());
			
			resultMap.put("schedule_1_day", dutyScoreSummary.getSchedule_1_day());
			resultMap.put("schedule_2_day", dutyScoreSummary.getSchedule_2_day());
			resultMap.put("schedule_3_day", dutyScoreSummary.getSchedule_3_day());
			resultMap.put("schedule_4_day", dutyScoreSummary.getSchedule_4_day());
			resultMap.put("schedule_5_day", dutyScoreSummary.getSchedule_5_day());
			resultMap.put("schedule_6_day", dutyScoreSummary.getSchedule_6_day());
			resultMap.put("schedule_7_day", dutyScoreSummary.getSchedule_7_day());
			resultMap.put("schedule_8_day", dutyScoreSummary.getSchedule_8_day());
			resultMap.put("schedule_9_day", dutyScoreSummary.getSchedule_9_day());
			resultMap.put("schedule_10_day", dutyScoreSummary.getSchedule_10_day());
			resultMap.put("schedule_11_day", dutyScoreSummary.getSchedule_11_day());
			resultMap.put("schedule_12_day", dutyScoreSummary.getSchedule_12_day());
			resultMap.put("schedule_13_day", dutyScoreSummary.getSchedule_13_day());
			resultMap.put("schedule_14_day", dutyScoreSummary.getSchedule_14_day());
			resultMap.put("schedule_15_day", dutyScoreSummary.getSchedule_15_day());
			resultMap.put("schedule_16_day", dutyScoreSummary.getSchedule_16_day());
			
			result = db.executeSql(sql, resultMap);
		} catch (DataAccessException e) {
			logger.error("修改加值班补贴失败", e);
		}
		return result;
	}

	/**
	 * 删除
	 * 
	 * @param id
	 * @return
	 */
	public int deleteDutyScoreSummaryById(long id) {
		DBExecuteUtil db = new DBExecuteUtil();
		int result = 0;
		try {
			String sql = " delete from bz_duty_score_summary where id=:id";
			Map resultMap = new HashMap();
			resultMap.put("id", Long.valueOf(id));
			result = db.executeSql(sql, resultMap);
		} catch (DataAccessException e) {
			logger.error("删除加值班补贴失败", e);
		}
		return result;
	}

	/**
	 * 根据id查询加值班补贴信息
	 * 
	 * @param id
	 * @return
	 */
	public DutyScoreSummary getDutyScoreSummaryById(long id) {
		DBExecuteUtil db = new DBExecuteUtil();
		DutyScoreSummary dutyScoreSummary = new DutyScoreSummary();
		try {
			String sql = "select a.*, b.name AS staffName, b.idcard from bz_duty_score_summary a, ecc_contact_info b where a.id=:id and a.staff_id = b.staff_id";

			Map parameters = new HashMap();
			parameters.put("id", Long.valueOf(id));
			dutyScoreSummary = (DutyScoreSummary) db.queryObject(sql, parameters, DutyScoreSummary.class);
		} catch (DataAccessException e) {
			logger.error("根据id获得加值班补贴失败", e);
		}
		return dutyScoreSummary;
	}

	/**
	 * 考勤列表，分页查询
	 * 
	 * @param pageNum  第几页
	 * @param pageSize 一页记录数
	 * @return
	 */
	public Object queryDutyScoreSummaryByPage(long dept_id, long month, String searchStr, int pageNum, int pageSize) {
		Map resultMap = new HashMap();
		int total = 0;
		List<DutyScoreSummary> list = new ArrayList();
		try {
			String sql = "select a.*, b.name AS staffName, b.idcard, c.NAME AS deptName from bz_duty_score_summary a, ecc_contact_info b, ecc_contact_group c "
					+ " where a.staff_id = b.id and a.dept_id=:dept_id and c.ID =a.dept_id and a.month=:month";
			if (StringUtils.isNotEmpty(searchStr)) {
				sql = sql + " and (b.name like :searchStr or b.idcard like :searchStr or b.code like :searchStr)";
			}
			
			Map<String, Object> params = new HashMap();
			params.put("dept_id", Long.valueOf(dept_id));
			params.put("searchStr", "%" + searchStr + "%");
			params.put("month", month);
			
			total = this.namedJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper(DutyScoreSummary.class)).size();

			if (pageSize == 0) {
				list = this.namedJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper(DutyScoreSummary.class));
			} else {
				list = this.namedJdbcTemplate.query(DBUtil.getPagingSql(sql.toString()),
						DBUtil.parsePagingParam(params, pageNum, pageSize), new BeanPropertyRowMapper(DutyScoreSummary.class));
			}
			
			for(DutyScoreSummary oneScoreSum : list) {
				String tempStr = oneScoreSum.getMonth() + "";
				String monthStr = tempStr.substring(0, 4) + "-" + tempStr.substring(4);
				oneScoreSum.setMonthStr(monthStr);
			}

		} catch (DataAccessException e) {
			logger.error("查询加值班补贴清单", e);
		}
		resultMap.put("total", Integer.valueOf(total));
		resultMap.put("list", list);
		return resultMap;
	}
	
	/**
	 * 查询某个部门的考勤列表
	 * 
	 * @param dept_id
	 * @param month
	 * @return
	 */
	public List<DutyScoreSummary> getDutyScoreSummaryList(long dept_id, long month) {
		DBExecuteUtil db = new DBExecuteUtil();
		String sql = "select * from bz_duty_score_summary where  month=:month";
		if(dept_id != 0) {
			sql += " and dept_id=:dept_id";
		}

		Map<String, Object> params = new HashMap();
		params.put("dept_id", Long.valueOf(dept_id));
		params.put("month", Long.valueOf(month));

		List<DutyScoreSummary> list = db.queryList(sql, params, DutyScoreSummary.class);
		return list;
	}

}
